﻿Imports Crystal.CommonDBManagement
Imports System.Threading
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports Crystal.CLL.CommonCLL

Public Class ImportFund

#Region "Declare"

    Private _ToatalExcelRows As Integer
    Private _IsReRun As Boolean
    Private _SheetID As Integer
    Private _SheetName As String
    Private _IsChkSheet As Boolean
    Private _Thread As Thread
    Private _FormatID As String
    Private _TotalExcelRows As Integer
    Private _IsThreadAborted As Boolean
    Private _IsChagned As Boolean = False
    Private _IsSelectedChagnes As Boolean = False
    Public Event LoadImportedFund(ByVal dtExcel As DataTable)
    Public WithEvents _FundTemplate As FundMapping
    Dim WithEvents CInvestorDetails As New cInvestorDetails
    Dim WithEvents CImportFunds As New cImportFunds

    Delegate Sub IncrementBySteps(ByVal pintStep As Integer)
    Delegate Sub GenricDelegate()
    Delegate Sub dlShowValidation(ByVal ldtValidationTypes As DataTable, ByVal DuplicateRecord As DataTable)
    Delegate Sub ControlVisiblity(ByVal isVisible As Boolean)
    Delegate Sub ShowState()

#End Region

#Region "On Load"

    Private Sub ImportFund_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If IsChildFormOpened() = False Then
            e.Cancel = True
        End If
    End Sub
    Private Sub ImportFund_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            InitControls()
            LoadControls()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in frmImportHistory_Load of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
        End Try
    End Sub
#End Region

#Region "Method"
    Private Sub InitControls()
        Try
            InitCombos()
            SetControls()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Function IsChildFormOpened() As Boolean
        For lmFomrs As Integer = 0 To Application.OpenForms.Count - 1
            If Application.OpenForms(lmFomrs).Name = "frmInvestorUploadValidations" Then
                Application.OpenForms(lmFomrs).BringToFront()
                Return False
            End If
        Next
        Return True
    End Function
    Private Sub InitCombos()
        With cmbSheets
            .ValueMember = "SNo."
            .DisplayMember = "Name"
        End With

        With cmbSourceName
            .ValueMember = "SourceID"
            .DisplayMember = "Source Name"
        End With

        With cmbTemplate
            .ValueMember = "TemplateID"
            .DisplayMember = "Template"
        End With

    End Sub

    Private Sub SetControls()
        Try
            cmbSheets.Enabled = False
            lblInProgress.Visible = False
            Me.Text = TitleManager.GetTitle("UploadFund")
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Private Sub LoadControls()
        Try
            LoadSourceNames()
            LoadTemplate()
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Private Sub LoadSourceNames()
        Dim eReturnVal As New ExecutionResult
        Dim dtSourceNames As New DataTable
        Dim clscImportFund As New cImportFunds
        Dim IDR As IDataReader = Nothing
        Try
            clscImportFund.SourceTypeCode = SourceTypeCode.FND.ToString()
            IDR = clscImportFund.SelectSourceNames
            dtSourceNames.Load(IDR)
            mdlCommon.ChangeDataReaderDataTable(dtSourceNames)
            If BLLmdlCommon.CheckNothingDataTable(dtSourceNames) Then Exit Sub
            Dim dr As DataRow
            dr = dtSourceNames.NewRow
            dr.Item("SourceID") = -1
            dr.Item("Source Name") = m_strSelectTextValue
            dtSourceNames.Rows.InsertAt(dr, 0)
            cmbSourceName.DataSource = dtSourceNames
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in LoadSourceNames of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            eReturnVal = Nothing
            dtSourceNames = Nothing
            IDR = Nothing
        End Try
        'mObjImportInvestor = Nothing
    End Sub

    Private Sub ClearAfterImportControls()
        pgbFund.Value = 0
        lblInProgress.Visible = False
    End Sub

    Private Sub LoadTemplate()
        If cmbSourceName.SelectedValue <= 0 Then Exit Sub
        Dim eReturnVal As New ExecutionResult
        Dim dtTemplate As New DataTable
        Dim clscImportFund As New cImportFunds
        Dim IDR As IDataReader = Nothing
        Try
            _IsChagned = True
            clscImportFund.ESourceID = cmbSourceName.SelectedValue
            IDR = clscImportFund.SelectImportTemplates
            dtTemplate.Load(IDR)
            mdlCommon.ChangeDataReaderDataTable(dtTemplate)
            If BLLmdlCommon.CheckNothingDataTable(dtTemplate) Then Exit Sub
            Dim dr As DataRow
            dr = dtTemplate.NewRow
            dr.Item("TemplateID") = m_strSelectIDValue
            dr.Item("Template") = m_strSelectTextValue
            dtTemplate.Rows.InsertAt(dr, 0)
            cmbTemplate.DataSource = dtTemplate
            _IsChagned = False
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in LoadTemplate of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            eReturnVal = Nothing
            dtTemplate = Nothing
            IDR = Nothing
        End Try
        'mObjImportInvestor = Nothing
    End Sub

    Private Function GetDataFromExcel(ByVal isColumDupliacte As Boolean, ByRef dtInvestor As DataTable) As DataTable
        'Create the temporary table to store data
        'Dim dtInvestor As New DataTable()
        Dim myDataColumn As DataColumn
        Dim myDataRow As DataRow

        'MIN/MAX Values used to frame the working size of the Excel data to be imported.
        Dim minCellRow As Integer = Int32.MaxValue
        Dim maxCellRow As Integer = Int32.MinValue
        Dim minCellColumn As Integer = Int32.MaxValue
        Dim maxCellColumn As Integer = Int32.MinValue

        Dim internalWorkBook As Infragistics.Documents.Excel.Workbook = Nothing
        internalWorkBook = Infragistics.Documents.Excel.Workbook.Load(txtAttach.Text)
        For Each row As Infragistics.Documents.Excel.WorksheetRow In internalWorkBook.Worksheets(_SheetName).Rows
            For Each cell As Infragistics.Documents.Excel.WorksheetCell In row.Cells
                If cell.Value IsNot Nothing Then
                    'Logic For Determining the Range of Rows/Columns in the Excel File.
                    minCellRow = Math.Min(minCellRow, cell.RowIndex)
                    maxCellRow = Math.Max(maxCellRow, cell.RowIndex)
                    minCellColumn = Math.Min(minCellColumn, cell.ColumnIndex)
                    maxCellColumn = Math.Max(maxCellColumn, cell.ColumnIndex)
                End If
            Next
        Next

        If isColumDupliacte = True Then
            For i As Integer = minCellColumn To maxCellColumn
                'Get the column name
                Dim columnName As String = internalWorkBook.Worksheets(_SheetName).Rows(minCellRow).Cells(i).Value.ToString()
                'The export that was demonstrated earlier utilizes the first row
                'for the column header.  We can now use that to give column names.
                myDataColumn = New DataColumn(columnName)
                dtInvestor.Columns.Add(myDataColumn)

            Next
        Else
            For i As Integer = minCellColumn To maxCellColumn

                Dim columnName As String = internalWorkBook.Worksheets(_SheetName).Rows(minCellRow).Cells(i).Value.ToString()
                'The export that was demonstrated earlier utilizes the first row
                'for the column header.  We can now use that to give column names.
                myDataColumn = New DataColumn(columnName)
                dtInvestor.Columns.Add(myDataColumn)
            Next
            For rowIndex As Integer = minCellRow + 1 To maxCellRow
                'Create a new DataRow
                myDataRow = dtInvestor.NewRow()
                'Loop through the columns and associate the value to each cell
                For columnIndex As Integer = minCellColumn To maxCellColumn
                    If internalWorkBook.Worksheets(_SheetName).Rows(rowIndex).Cells(columnIndex).Value Is Nothing Then
                        myDataRow(columnIndex) = DBNull.Value
                    Else
                        myDataRow(columnIndex) = internalWorkBook.Worksheets(_SheetName).Rows(rowIndex).Cells(columnIndex).Value
                    End If
                Next
                'Add The Row to a DataTable
                dtInvestor.Rows.Add(myDataRow)
            Next
            Dim lintFilingDateIndex As Integer = -1
            Dim result As DateTime
            Dim lCultureInfo As System.Globalization.CultureInfo = Nothing
            Dim lenmDateFormatStatus As DateFormatStatus
            Dim lintFirstValidFilingDateRow As Integer
            lintFilingDateIndex = dtInvestor.Columns("DeliveryDate").Ordinal
            If lintFilingDateIndex <> -1 Then
                Try
                    'Get the row index of first valid filing date to get the culture info and date format of Filing Date
                    For rowIndex As Integer = 0 To dtInvestor.Rows.Count - 1
                        If dtInvestor.Rows(rowIndex).Item(lintFilingDateIndex) IsNot DBNull.Value Then
                            lintFirstValidFilingDateRow = rowIndex
                            Exit For
                        End If
                    Next
                    'If the selected culture is UK
                    result = DateTime.ParseExact(dtInvestor.Rows(lintFirstValidFilingDateRow).Item(lintFilingDateIndex).ToString, supportedFormats, System.Globalization.CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB"), System.Globalization.DateTimeStyles.None)
                    lenmDateFormatStatus = DateFormatStatus.UK
                Catch ex As Exception
                    Try
                        result = DateTime.ParseExact(dtInvestor.Rows(lintFirstValidFilingDateRow).Item(lintFilingDateIndex).ToString, supportedFormats, System.Globalization.CultureInfo.GetCultureInfoByIetfLanguageTag("en-US"), System.Globalization.DateTimeStyles.None)
                        'If the selected culture is US
                        lenmDateFormatStatus = DateFormatStatus.US
                    Catch ex1 As Exception
                        lenmDateFormatStatus = DateFormatStatus.OTHERS
                        ShowMessageBox(DBResult.EDS_UPLD_FLNG_DATE_OTHERS)
                    End Try
                End Try
                If lenmDateFormatStatus = DateFormatStatus.UK Then
                    lCultureInfo = New System.Globalization.CultureInfo("en-GB")
                ElseIf lenmDateFormatStatus = DateFormatStatus.US Then
                    lCultureInfo = New System.Globalization.CultureInfo("en-US")
                End If
            End If
            'Dim supportedFormats() As String = New String() {"dd-MM-yyyy", "dd-MM-yy", "dd-MM-yy HH:mm:ss tt", "dd-MM-yyyy HH:mm:ss tt", "dd-MMM-yy", "dd-MMM-yyyy HH:mm:ss tt", "dd-MMM-yy HH:mm:ss tt", "dd/MM/yyyy", "dd/MM/yy", "dd/MM/yy HH:mm:ss tt", "dd/MM/yyyy HH:mm:ss tt", "dd/MMM/yy", "dd/MMM/yyyy HH:mm:ss tt", "dd/MMM/yy HH:mm:ss tt", _
            '                                             "MM-dd-yyyy", "MM-dd-yy", "MM-dd-yy HH:mm:ss tt", "MM-dd-yyyy HH:mm:ss tt", "MMM-dd-yy", "MMM-dd-yyyy HH:mm:ss tt", "M-dd-yyyy HH:mm:ss tt", "mm-dd-yyyy HH:mm:ss tt", "MMM-dd-yy HH:mm:ss tt", "MM/dd/yyyy", "MM/dd/yy", "MM/dd/yy HH:mm:ss tt", "MM/dd/yyyy HH:mm:ss tt", "MMM/dd/yy", "MMM/dd/yyyy HH:mm:ss tt", "MMM/dd/yy HH:mm:ss tt", _
            '                                             "d/MM/yyyy HH:mm:ss tt", "MM/d/yyyy HH:mm:ss tt", "M/dd/yyyy HH:mm:ss tt", "dd/M/yyyy HH:mm:ss tt", "d/MM/yyyy", "MM/d/yyyy", "M/dd/yyyy", "dd/M/yyyy", "d-MM-yyyy HH:mm:ss tt", "MM-d-yyyy HH:mm:ss tt", "M-dd-yyyy HH:mm:ss tt", "dd-M-yyyy HH:mm:ss tt", "d-MM-yyyy", "MM-d-yyyy", "M-dd-yyyy", "dd-M-yyyy", _
            '                                             "d/M/yyyy HH:mm:ss tt", "M/d/yyyy HH:mm:ss tt", "d/M/yyyy", "M/d/yyyy", "d-M-yyyy HH:mm:ss tt", "M-d-yyyy HH:mm:ss tt", "d-M-yyyy", "M-d-yyyy"}


            For rowIndex As Integer = 0 To dtInvestor.Rows.Count - 1
                Try
                    If dtInvestor.Rows(rowIndex).Item(lintFilingDateIndex) Is DBNull.Value Then
                        'If the Filing Date is null, then do nothing
                        Continue For
                    End If
                    'If Filing Date is a valid date, then update the FilingDateCopy column
                    result = DateTime.ParseExact(dtInvestor.Rows(rowIndex).Item(lintFilingDateIndex).ToString, supportedFormats, lCultureInfo, System.Globalization.DateTimeStyles.None)
                    dtInvestor.Rows(rowIndex).Item("FilingDateCopy") = Format(result, "MM-dd-yy")
                Catch ex As Exception
                    ShowMessageBox(DBResult.EDS_UPLD_FLNG_DATE_OTHERS)
                End Try
            Next
        End If
        Return dtInvestor

    End Function

    Private Function GetWorksheetNames(ByVal file As String) As Boolean
        Dim ReturnVal As Boolean = False
        Dim ExcelHandling As New ExcelHandling
        Dim WorkBooks As New DataTable

        Try
            'WorkBooks = ExcelHandling.GetWorksheetNames(file)
            Dim internalWorkBook As Infragistics.Documents.Excel.Workbook = Nothing
            internalWorkBook = Infragistics.Documents.Excel.Workbook.Load(txtAttach.Text)
            Dim WorkBook As DataRow
            WorkBooks.Columns.Add("SNo.", GetType(Integer))
            WorkBooks.Columns.Add("Name", GetType(String))
            WorkBook = WorkBooks.NewRow()
            WorkBook("SNo.") = -1
            WorkBook("Name") = "---Select---"
            'WorkBook("Name") = ContentManager.GetContent("SELECT")
            WorkBooks.Rows.InsertAt(WorkBook, 0)
            If internalWorkBook.Worksheets.Count > 0 Then
                For i As Integer = 0 To internalWorkBook.Worksheets.Count - 1
                    WorkBook = WorkBooks.NewRow()
                    WorkBook("SNo.") = i + 1
                    WorkBook("Name") = internalWorkBook.Worksheets(i).Name
                    WorkBooks.Rows.Add(WorkBook)
                    WorkBook = WorkBooks.NewRow()
                Next
            End If
            internalWorkBook = Nothing
            If CommonCLL.CheckEmptyDataTable(WorkBooks) Then
                ReturnVal = False
            Else
                cmbSheets.Enabled = True
                cmbSheets.DataSource = WorkBooks
                If _IsReRun Then
                    cmbSheets.SelectedIndex = _SheetID
                Else
                    If WorkBooks.Rows.Count > 0 Then
                        'If WorkBooks.Rows.Count = 2 Then
                        cmbSheets.SelectedIndex = 1
                        'End If
                    End If
                End If
                ReturnVal = True
            End If

            btnRun.Enabled = True
        Catch ex As Exception
            MsgBox(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in GetWorksheetNames of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
            ReturnVal = False
        Finally
            ExcelHandling = Nothing
            WorkBooks = Nothing
        End Try
        Return ReturnVal
    End Function

    Private Function Validations() As Boolean
        Dim BoolCheck As Boolean = True
        If CommonCLL.IsEmpty(txtAttach.Text) Then
            MessageBox.Show("File name can not be left blank.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtAttach.Focus()
            BoolCheck = False
            Return BoolCheck
        End If
        If Not IO.File.Exists(Trim(txtAttach.Text)) Then
            MessageBox.Show("Please select a valid file name.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtAttach.Focus()
            BoolCheck = False
            Return BoolCheck
        End If

        If CommonCLL.CheckEmptyDataTable(CType(cmbSheets.DataSource, DataTable)) Then
            MessageBox.Show("Please select a valid file name.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            cmbSheets.Focus()
            BoolCheck = False
            Return BoolCheck
        Else
            If CType(cmbSheets.SelectedValue, DataRowView).Item("SNo.") <= 0 Then
                MessageBox.Show("Please select a sheet name.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                cmbSheets.Focus()
                BoolCheck = False
                Return BoolCheck
            End If
        End If
        If cmbTemplate.SelectedValue <= 0 Then
            MessageBox.Show("Please select a format name.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            cmbTemplate.Focus()
            BoolCheck = False
            Return BoolCheck
        End If

        If BlnCheckMandatory() = False Then
            BoolCheck = False
            Return BoolCheck
        End If

        Return BoolCheck
    End Function

    Private Sub SetContolsOnImportClick(ByVal value As Boolean)
        Try
            If value Then
                btnClear.Enabled = False
                gpFundImport.Enabled = False
                btnRun.Enabled = False
            Else
                btnPause.Enabled = False
                btnContinue.Enabled = False
                btnCancel.Enabled = False
                btnClear.Enabled = False
                gpFundImport.Enabled = True
                lblInProgress.Visible = False
                btnClose.Enabled = True
            End If
        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in SetContolsOnImportClick of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try
    End Sub
    Public Sub ButtonVisiblity(ByVal Val As Boolean)
        btnPause.Enabled = Val
        btnCancel.Enabled = Val
        lblInProgress.Text = " System is checking for file consistency. Please wait..."

    End Sub

    Public Sub VisiblityLabels(ByVal Val As Boolean)
        lblInProgress.Visible = Val

    End Sub

    Public Sub LabelMessages(ByVal Val As Boolean)
        If Val = True Then
            lblInProgress.Text = " System is fetching data from excel file. Please wait..."
        Else
            lblInProgress.Text = " Please wait. Data Import process is in progress......"
        End If

    End Sub

    ''' <summary>
    ''' This will set the visibility of reimport button, which can be enabled after sytem catch the excel in validation
    ''' </summary>
    ''' <param name="Val"></param>
    ''' <remarks></remarks>
    ''' 
    Private Sub VisibilityReimportButton(ByVal Val As Boolean)
        btnReRun.Enabled = Val
        btnPause.Enabled = False
        btnCancel.Enabled = False
        If btnReRun.Enabled Or btnRun.Enabled Then
            lblInProgress.Visible = False
            btnClose.Enabled = True
        End If
        'btn
    End Sub

    Private Sub VisiblityImportButton(ByVal val As Boolean)
        btnRun.Enabled = val
        btnClose.Enabled = val
        gpFundImport.Enabled = val
        cmbSourceName.Enabled = val
        cmbTemplate.Enabled = val
        btnTemplate.Enabled = val
        cmbSheets.Enabled = val
    End Sub
    Private Sub ResetOrdinal(ByVal Fund As DataTable)
        Dim dtFundClone As DataTable = Fund.Clone()
        Dim intCounter As Integer
        Dim IsCounterChanged As Boolean = False
        While intCounter < dtFundClone.Columns.Count
            If dtFundClone.Columns(0).ColumnName <> "FndEntityID" Then
                Fund.Columns("FndEntityID").SetOrdinal(0)
                IsCounterChanged = True
                'Exit While
            End If
            If dtFundClone.Columns(1).ColumnName <> "FundName" Then
                Fund.Columns("FundName").SetOrdinal(1)
                IsCounterChanged = True
                'Exit While
            End If
            If dtFundClone.Columns(2).ColumnName <> "Type" Then
                Fund.Columns("Type").SetOrdinal(2)
                IsCounterChanged = True
                'Exit While
            End If
            If dtFundClone.Columns(3).ColumnName <> "Style" Then
                Fund.Columns("Style").SetOrdinal(3)
                IsCounterChanged = True
                'Exit While
            End If
            If dtFundClone.Columns(4).ColumnName <> "Turnover" Then
                Fund.Columns("Turnover").SetOrdinal(4)
                IsCounterChanged = True
                'Exit While'
            End If
            If dtFundClone.Columns(5).ColumnName <> "Country" Then
                Fund.Columns("Country").SetOrdinal(5)
                IsCounterChanged = True
            End If
            If IsCounterChanged Then
                intCounter = 0
                IsCounterChanged = False
            Else
                intCounter += 1
            End If
            dtFundClone = Fund.Clone()
        End While
        If Fund.Columns.Contains("LastReportDate") = False Then
            Fund.Columns.Add("LastReportDate", GetType(Date))
        End If
        Fund.Columns("LastReportDate").SetOrdinal(6)

        If Fund.Columns.Contains("PortFolioValue") = False Then
            Fund.Columns.Add("PortFolioValue", GetType(String))
        End If
        Fund.Columns("PortfolioValue").SetOrdinal(7)
        If Fund.Columns.Contains("PERatio") = False Then
            Fund.Columns.Add("PERatio", GetType(String))
        End If
        Fund.Columns("PERatio").SetOrdinal(8)
        If Fund.Columns.Contains("PBRatio") = False Then
            Fund.Columns.Add("PBRatio", GetType(String))
        End If
        Fund.Columns("PBRatio").SetOrdinal(9)
        If Fund.Columns.Contains("DividendYield") = False Then
            Fund.Columns.Add("DividendYield", GetType(String))
        End If
        Fund.Columns("DividendYield").SetOrdinal(10)
        If Fund.Columns.Contains("SalesGrowth") = False Then
            Fund.Columns.Add("SalesGrowth", GetType(String))
        End If
        Fund.Columns("SalesGrowth").SetOrdinal(11)
        If Fund.Columns.Contains("PriceMomentum") = False Then
            Fund.Columns.Add("PriceMomentum", GetType(String))
        End If
        Fund.Columns("PriceMomentum").SetOrdinal(12)
        If Fund.Columns.Contains("RelativeStrength") = False Then
            Fund.Columns.Add("RelativeStrength", GetType(String))
        End If
        Fund.Columns("RelativeStrength").SetOrdinal(13)
        If Fund.Columns.Contains("Beta") = False Then
            Fund.Columns.Add("Beta", GetType(String))
        End If
        Fund.Columns("Beta").SetOrdinal(14)
        'Fund.Columns.Remove("Message")
        'Fund.Columns.Remove("InvestorName")
        If Fund.Columns.Contains("FundTypeID") = False Then
            Fund.Columns.Add("FundTypeID", GetType(Integer))
        End If
        Fund.Columns("FundTypeID").SetOrdinal(15)
        If Fund.Columns.Contains("StyleID") = False Then
            Fund.Columns.Add("StyleID", GetType(Integer))
        End If
        Fund.Columns("StyleID").SetOrdinal(16)
        If Fund.Columns.Contains("TurnoverID") = False Then
            Fund.Columns.Add("TurnoverID", GetType(Integer))
        End If
        Fund.Columns("TurnoverID").SetOrdinal(17)
        If Fund.Columns.Contains("CountryID") = False Then
            Fund.Columns.Add("CountryID", GetType(Integer))
        End If
        Fund.Columns("CountryID").SetOrdinal(18)
        If Fund.Columns.Contains("InvEntityID") = False Then
            Fund.Columns.Add("InvEntityID", GetType(String))
        End If
        Fund.Columns("InvEntityID").SetOrdinal(19)
        If Fund.Columns.Contains("FundID") = False Then
            Fund.Columns.Add("FundID", GetType(String))
        End If
        Fund.Columns("FundID").SetOrdinal(20)
        If Fund.Columns.Contains("InvestorID") = False Then
            Fund.Columns.Add("InvestorID", GetType(String))
        End If
        Fund.Columns("InvestorID").SetOrdinal(21)
        If Fund.Columns.Contains("EStatusID") = False Then
            Fund.Columns.Add("EStatusID", GetType(Integer))
        End If
        Fund.Columns("EStatusID").SetOrdinal(22)
        If Fund.Columns.Contains("RowNumber") = False Then
            Fund.Columns.Add("RowNumber", GetType(Integer))
        End If
        Fund.Columns("RowNumber").SetOrdinal(23)
        dtFundClone = Nothing
    End Sub


    ''' <summary>
    ''' 'Set the Cursor Status
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetCursorandLabelToNormal()
        Me.Cursor = Cursors.Default
        lblInProgress.Visible = False
    End Sub

    Private Sub DisableButtons(ByVal bool As Boolean)
        btnPause.Enabled = bool
        btnCancel.Enabled = bool
        btnClear.Enabled = bool
        btnContinue.Enabled = bool
        cmbSourceName.Enabled = bool
        cmbTemplate.Enabled = bool
        btnRun.Enabled = bool
        btnReRun.Enabled = bool
    End Sub
    ''' <summary>
    '''  To Show the End Result of Import Process
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub ImportedDataDetails()
        MessageBox.Show("Data imported successfully.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
        lblInProgress.Visible = False
        btnReRun.Enabled = False
        gpFundImport.Enabled = False
        btnClose.Text = "&OK"
    End Sub

    Private Sub IncrementProgressBarSteps(ByVal pintCount As Integer)
        Dim lintStartValue As Integer = pgbFund.Value
        Dim lintCounter As Integer = lintStartValue
        While lintCounter <= lintStartValue + pintCount
            pgbFund.IncrementValue(pgbFund.Step)
            lintCounter += 1
        End While
        Application.DoEvents()
    End Sub
    Private Sub ProgressBarCompleteStart()
        Invoke(New GenricDelegate(AddressOf CompleteProgressBar))
        InitProgressBar()
    End Sub
    ''' <summary>
    ''' Function to call the Class's function that Import data in the database
    ''' </summary>
    ''' <remarks></remarks>
    ''' 

    Private Sub ImportData()
        Dim Fund As New DataTable
        Dim ExcelClone As New DataTable
        Dim IsColumDupliacte As Boolean = False
        Dim LoadInvestor As GenricDelegate
        Dim deadlockerror As Boolean = False
        Dim Logmessage As New System.Text.StringBuilder
        Try
            Me.Invoke(New ControlVisiblity(AddressOf VisiblityLabels), True)
            Me.Invoke(New ControlVisiblity(AddressOf LabelMessages), True)
            Me.Invoke(New ControlVisiblity(AddressOf VisiblityImportButton), False)
            InitProgressBar()
            Thread.Sleep(3000)
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 5)
            Me.Invoke(New ControlVisiblity(AddressOf LabelMessages), False)
            Me.Invoke(New ControlVisiblity(AddressOf ButtonVisiblity), True)
            If Not GetExcelFileData(IsColumDupliacte, False, Fund, txtAttach.Text, _SheetName, _TotalExcelRows) Then
                If IsColumDupliacte = False Then
                    MessageBox.Show("Please select correct file to import fund data.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Me.Invoke(New GenricDelegate(AddressOf ClearAfterImportControls))
                    Me.Invoke(New ControlVisiblity(AddressOf VisiblityImportButton), True)
                    Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), False)
                    '***********************************************
                End If
                ProgressBarCompleteStart()
                Exit Sub
            End If

            If Not MapWithDatabaseColumns(_FormatID, Fund, ExcelClone) Then
                SetCursorandLabelToNormal()
                Me.Invoke(New GenricDelegate(AddressOf ClearAfterImportControls))
                Me.Invoke(New ControlVisiblity(AddressOf VisiblityLabels), False)
                Me.Invoke(New ControlVisiblity(AddressOf VisiblityImportButton), True)
                Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), True)
                ProgressBarCompleteStart()
                '**************************************************
                Exit Sub
            End If

            If Not IsFieldsMapped(ExcelClone, _FormatID) Then
                Me.Invoke(New GenricDelegate(AddressOf ClearAfterImportControls))
                Me.Invoke(New ControlVisiblity(AddressOf VisiblityImportButton), True)
                Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), False)
                ProgressBarCompleteStart()

                '**********************************************
                Exit Sub
            End If


            ' If Not ValidateFile(Fund, ExcelClone) Then Exit Sub

            Dim dtTemplateFields As DataTable = Nothing
            CImportFunds.ClearBulkFundImport("FND_BulkImportValidation")
            ResetOrdinal(Fund)
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 15)

            Dim sqlBulkCopy As New SqlClient.SqlBulkCopy(CommonCLL.mstrConnectionstring)
            sqlBulkCopy.DestinationTableName = "FND_BulkImportValidation"
            For i As Integer = 0 To Fund.Columns.Count - 1 Step 1
                sqlBulkCopy.ColumnMappings.Add(i, i + 1)
            Next
            sqlBulkCopy.WriteToServer(Fund)
            sqlBulkCopy.Close()
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 10)

            If Not ValidateFile(Fund, ExcelClone) Then Exit Sub
            BackUpDatabase()
            CImportFunds.ClearBulkFundImport("FND_BulkFundImport")

            Dim dTable As DataSet = CImportFunds.GetValidatedData()
            Dim table As DataTable = dTable.Tables(0)
            sqlBulkCopy = New SqlClient.SqlBulkCopy(CommonCLL.mstrConnectionstring)
            sqlBulkCopy.DestinationTableName = "FND_BulkFundImport"
            sqlBulkCopy.WriteToServer(table)
            sqlBulkCopy.Close()

            If CImportFunds.ImportIFunds(table) Then
                Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), False)
                Me.Invoke(New ControlVisiblity(AddressOf SetContolsOnImportClick), False)

                '************************************************
                Invoke(New GenricDelegate(AddressOf CompleteProgressBar))
                Invoke(New ShowState(AddressOf ImportedDataDetails))
                Application.DoEvents()

                RaiseEvent LoadImportedFund(table)
                '''''Logging

                Logmessage.Append("Format ID - ")
                Logmessage.Append(CType(_FormatID, String))
                Logmessage.Append(", Sheet Name - ")
                Logmessage.Append(_SheetName)
                Logmessage.Append(Environment.NewLine)
                Logmessage.Append("Fund import completed successfully.")
                LogErrorToDatabase(Logmessage.ToString())
                'Me.LogErrorToDatabase(Logmessage)
                ''''''''''''''''''''''''''''
            Else
                Application.DoEvents()
            End If
        Catch ex As Exception
            Application.DoEvents()
            '''''Logging
            Logmessage.Append("Format ID - ")
            Logmessage.Append(CType(_FormatID, String))
            Logmessage.Append(", Sheet Name - ")
            Logmessage.Append(_SheetName)
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Fund import can not be completed successfully.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.Message.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            ''''''''''''''''''''''''''''
            If (ex.Message.ToString().Substring(ex.Message.ToString().LastIndexOf(" ")).Trim() = "1205") Then
                deadlockerror = True
            End If
            If _IsThreadAborted = False Then
                If (deadlockerror) Then
                    MessageBox.Show(ex.Message.Substring(0, ex.Message.ToString().LastIndexOf(" ")), m_strMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    MessageBox.Show(ex.Message, m_strMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If

            End If
        Finally
            Fund = Nothing
            ExcelClone = Nothing
            Logmessage = Nothing
            LoadInvestor = Nothing
            If _IsThreadAborted = False Then
                ' Me.Invoke(New ControlVisiblity(AddressOf SetContolsOnImportClick), False)
                If (deadlockerror) Then
                    Me.Invoke(New GenricDelegate(AddressOf DisableControl))
                End If
            End If

        End Try


    End Sub

    Private Sub DisableControl()
        btnRun.Enabled = True
    End Sub

    Private Function BlnCheckMandatory() As Boolean
        Dim ReturnVal As ExecutionResult
        Dim CImportFund As New cImportFunds
        Try
            CImportFund.TemplateID = cmbTemplate.SelectedValue
            CImportFund.SourceTypeCode = SourceTypeCode.FND.ToString()
            ' m_iFormatID = cmbFormat.SelectedValue
            ReturnVal = CImportFund.ChkImportFundIsIsmandatory()
            If ReturnVal.ResultParamenters(2).Value = "" Then
                Return True
            Else
                MessageBox.Show("Please Map Manadatory field '" & ReturnVal.ResultParamenters(2).Value & "'", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return False
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in BlnCheckMandatory of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            ReturnVal = Nothing
            CImportFund = Nothing
        End Try
        Return True

    End Function

    Private Sub Import()
        Try
            If Not _Thread Is Nothing Then
                If _Thread.IsAlive Then
                    Exit Sub
                End If
            End If
            ClearAfterImportControls()
            Application.DoEvents()

            If Validations() Then
                _IsThreadAborted = False
                _SheetName = CType(cmbSheets.SelectedItem, DataRowView).Item("Name")
                _FormatID = cmbTemplate.SelectedValue
                SetContolsOnImportClick(True)
                gpFundImport.Enabled = False
                btnRun.Enabled = True
                btnClear.Enabled = False
                cmbSheets.Enabled = False
                _Thread = New Thread(AddressOf ImportData)
                _Thread.IsBackground = True
                _Thread.Start()
            End If

        Catch ex As Exception
            SetContolsOnImportClick(False)
            MsgBox(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnImport_Click of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally

            '  gpInvestorImport.Enabled = True
        End Try
    End Sub

    ''' <summary>
    '''  'Completing the ProgressBar
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub CompleteProgressBar()
        If pgbFund.InvokeRequired = False Then
            While pgbFund.Value < pgbFund.Maximum
                IncrementProgressBar()
            End While
        Else
            Me.pgbFund.Invoke(New GenricDelegate(AddressOf CompleteProgressBar))
        End If

    End Sub

    ''' <summary>
    '''   'Incrementing the Progress Bar
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub IncrementProgressBar() 'Handles ObjcInvestorDetails.entInvRecordAdded
        pgbFund.IncrementValue(pgbFund.Step)
        Application.DoEvents()
    End Sub

    ''' <summary>
    ''' 'To Initialize the Progressbar
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub InitProgressBar()
        If pgbFund.InvokeRequired = False Then
            With pgbFund
                .Value = 0
                .Step = 1
                .Maximum = 100 ' _TotalExcelRows ' dtExcel.Rows.Count - 1
                .Show()
            End With
        Else
            Me.pgbFund.Invoke(New GenricDelegate(AddressOf InitProgressBar))
        End If

    End Sub

#End Region

#Region "Button Events"

    Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
        Dim ObjImportFile As New OpenFileDialog
        Try
            If _IsReRun Then
                GetWorksheetNames(Trim(txtAttach.Text))
            Else
                With ObjImportFile
                    '.Filter = "Excel (97-2003 format)(*.xls)|*.xls|Excel (2007-2010 format)(*.xlsx)|*.xlsx"
                    .Filter = "Excel (2007-2010 format)(*.xlsx)|*.xlsx|Excel (97-2003 format)(*.xls)|*.xls"
                    .Multiselect = False
                    '.InitialDirectory = "D:\"
                    .InitialDirectory = "C:\"
                    .DefaultExt = ".xlsx"
                    If .ShowDialog() <> Windows.Forms.DialogResult.Cancel Then
                        txtAttach.Text = .FileName
                        Application.DoEvents()
                        Try
                            If (Not CommonCLL.IsEmpty(txtAttach.Text)) AndAlso IO.File.Exists(Trim(txtAttach.Text)) Then
                                InitCombos()
                                If GetWorksheetNames(Trim(txtAttach.Text)) Then
                                    cmbSheets.Enabled = True
                                Else
                                    MessageBox.Show("Please select a valid file.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                                    Exit Sub
                                End If
                            End If
                        Catch ex As Exception
                            MsgBox(ex.Message)
                            '''''Logging
                            Dim Logmessage As New System.Text.StringBuilder
                            Logmessage.Append("There is error in btnBrowse_Click of form import fund.")
                            Logmessage.Append(Environment.NewLine)
                            Logmessage.Append("Error Message - ")
                            Logmessage.Append(ex.ToString())
                            Me.LogErrorToDatabase(Logmessage.ToString)
                            Logmessage = Nothing
                            ''''''''''''''''''''''''''''
                        End Try
                    End If
                End With
                btnReRun.Enabled = False
                '***********************************************
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnBrowse_Click of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            ObjImportFile = Nothing
        End Try
    End Sub

    Private Sub btnTemplate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTemplate.Click
        Dim IsColumDupliacte As Boolean = False
        Dim ExcelColumns As New DataTable
        Dim mTemplateID As Integer = 0
        Dim table As DataTable
        Dim FilterQuery As String
        Dim Excel() As DataRow
        Try
            _SheetName = CType(cmbSheets.SelectedItem, DataRowView).Item("Name")
            If Not GetExcelFileData(IsColumDupliacte, True, ExcelColumns, txtAttach.Text, _SheetName, _TotalExcelRows) Then
                If IsColumDupliacte = False Then
                    MessageBox.Show("Please select correct file to import fund data.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
                Exit Sub
            End If
            Dim uc As New UCTemplateMapping(False, BLLmdlCommon.strConfiguration)
            uc.SourceID = cmbSourceName.SelectedValue
            uc.FormatID = cmbTemplate.SelectedValue
            mTemplateID = uc.FormatID
            uc.enmSourceTypeCode = SourceTypeCode.FND
            uc.mdtExcelClone = ExcelColumns
            _FundTemplate = New FundMapping()
            AddHandler _FundTemplate.ReloadTemplateId, AddressOf Reload
            With _FundTemplate
                .StartPosition = FormStartPosition.CenterScreen
                .Controls.Add(uc)
                .Width = uc.Width + 10
                .Height = uc.Height + 30
                .ShowDialog()
            End With
            If mTemplateID <> uc.FormatID Then
                table = cmbTemplate.DataSource
                If table.Columns.Contains("TemplateID") Then

                    FilterQuery = "TemplateID ='" + Trim(uc.FormatID).Replace("'", "''") + "'"
                    Excel = table.Select(FilterQuery)
                    If Excel.Length = 0 Then
                        LoadTemplate()
                    End If
                    cmbTemplate.SelectedValue = uc.FormatID
                End If
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnFormat_Click of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            ExcelColumns = Nothing
            table = Nothing
            Excel = Nothing
        End Try
    End Sub

    Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
        Import()
    End Sub

    Private Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Try
            'If (Not CInvestorDetails.trans Is Nothing) Then
            '    CInvestorDetails.trans.Rollback()
            'End If

            _IsThreadAborted = True
            ClearAfterImportControls()
            If Not _Thread Is Nothing Then

                If _Thread.IsAlive Then
                    If _Thread.ThreadState = 68 Then
                        _Thread.Resume()
                    End If
                    _Thread.Abort()
                    _Thread = Nothing
                    '      _Thread.Join()
                End If
            End If
            SetContolsOnImportClick(False)
            'btnRun.Enabled = True
        Catch ex1 As ThreadAbortException

        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnCancel_Click of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try
    End Sub

    Private Sub btnClear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClear.Click
        Try
            ClearAllFields()
            btnClear.Enabled = False
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Private Sub ClearAllFields()
        If Not cmbSheets.SelectedValue Is Nothing Then
            cmbSheets.SelectedIndex = 0
        End If
        If Not cmbSourceName.SelectedValue Is Nothing Then
            cmbSourceName.SelectedIndex = 0
        End If
        If Not cmbTemplate.SelectedValue Is Nothing Then
            cmbTemplate.SelectedIndex = -1
        End If
        cmbSourceName.Enabled = False
        cmbSheets.Enabled = False
        cmbTemplate.Enabled = False
        btnTemplate.Enabled = False
        txtAttach.Text = ""
        ClearAfterImportControls()
        _IsReRun = False
    End Sub

    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnReRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReRun.Click
        _IsReRun = True
        _SheetID = cmbSheets.SelectedIndex

        btnBrowse_Click(sender, e)
        Import()
        btnReRun.Enabled = False
        'Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), True)
    End Sub

    Private Sub btnPause_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPause.Click
        Try
            _Thread.Suspend()
            btnPause.Enabled = False
            btnContinue.Enabled = True
            lblInProgress.Focus()
        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnPause_Click of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try
    End Sub

    Private Sub btnContinue_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnContinue.Click
        Try
            _Thread.Resume()
            btnPause.Enabled = True
            btnContinue.Enabled = False
            lblInProgress.Focus()
        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnContinue_Click of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try
    End Sub
#End Region

#Region "ComboEvents"

    Private Sub cmbSourceName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSourceName.SelectedIndexChanged
        Try
            _IsChagned = True
            btnRun.Enabled = False
            LoadTemplate()
            If cmbSourceName.SelectedIndex > 0 Then
                cmbTemplate.Enabled = True
                btnTemplate.Enabled = True
                If DirectCast(cmbTemplate.DataSource, DataTable).Rows.Count > 1 Then
                    cmbTemplate.SelectedIndex = 1
                Else
                    cmbTemplate.SelectedIndex = 0
                End If
                btnClear.Enabled = True
                If _IsChkSheet = True Then
                    btnRun.Enabled = True
                End If
            Else
                If cmbTemplate.DataSource IsNot Nothing Then
                    cmbTemplate.SelectedIndex = -1
                    cmbTemplate.Enabled = False
                    btnTemplate.Enabled = False
                End If
            End If
            _IsChagned = False
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in cmbFormat_SelectedIndexChanged of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try
    End Sub

    Private Sub cmbTemplate_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbTemplate.SelectedIndexChanged
        Dim dsReturn As DataSet = Nothing
        Dim data As DataTable = Nothing
        Dim Id As String = String.Empty
        Dim Style As String = String.Empty
        Dim FilterQuery As String
        Dim Excel() As DataRow
        Try
            btnRun.Enabled = False
            If cmbTemplate.SelectedIndex > 0 Then
                _FormatID = cmbTemplate.SelectedValue
                If _IsChkSheet = True Then
                    btnRun.Enabled = True
                End If
            End If
            If _IsChagned = False Then
                If cmbTemplate.SelectedValue = m_strNewIDValue Then
                    Dim objAddValue As New CrystalCommon.View.AddValue(enmEntityType.Source.ToString(), LKPMaster.TMPLT, "Template Name")
                    objAddValue.StartPosition = FormStartPosition.CenterScreen
                    objAddValue.ParameterInt = cmbSourceName.SelectedValue
                    objAddValue.ShowDialog()
                    Id = objAddValue.ID
                    If Id <> String.Empty Then
                        Dim obj As New cImportFunds()
                        dsReturn = CImportFunds.GetLatestAddedTemplate(Id)
                        If dsReturn IsNot Nothing Then
                            Style = dsReturn.Tables(0).Rows(0)("Name").ToString()
                            Id = dsReturn.Tables(0).Rows(0)("ETemplateID").ToString()
                            Dim table As DataTable = cmbTemplate.DataSource
                            If table.Columns.Contains("TemplateID") Then
                                FilterQuery = "TemplateID ='" + Trim(Id).Replace("'", "''") + "'"
                                Excel = table.Select(FilterQuery)
                                If Excel.Length = 0 Then
                                    If AddRowInDropDownAtLast(table, cmbTemplate.ValueMember, cmbTemplate.DisplayMember, Id, Style) = False Then
                                        mdlCommon.ShowMessage("Error in accessing Template drop down.", MessageBoxIcons.Error)
                                        Exit Sub
                                    End If
                                End If
                                cmbTemplate.SelectedValue = Id
                            End If
                        End If
                    Else
                        cmbTemplate.SelectedValue = _FormatID
                    End If
                End If
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in cmbFormat_SelectedIndexChanged of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            dsReturn = Nothing
            data = Nothing
            FilterQuery = String.Empty
            Excel = Nothing
        End Try
    End Sub

    Private Sub cmbSheets_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSheets.SelectedIndexChanged

        If _IsReRun Then Exit Sub
        Dim IsColumDupliacte As Boolean = False
        Try
            ClearAfterImportControls()
            _IsChkSheet = False
            cmbSourceName.Enabled = False
            cmbTemplate.Enabled = False
            btnTemplate.Enabled = False
            If Not cmbTemplate.SelectedIndex > 0 Then
                btnRun.Enabled = False
            End If
            If cmbSheets.SelectedIndex > 0 Then
                lblInProgress.Text = "Please Wait!"
                cmbSourceName.Enabled = True
                cmbTemplate.Enabled = True
                btnTemplate.Enabled = True
                If cmbSourceName.SelectedIndex <> -1 Then
                    If DirectCast(cmbSourceName.DataSource, DataTable).Rows.Count > 0 Then
                        cmbSourceName.SelectedIndex = 1
                    Else
                        cmbSourceName.SelectedIndex = 0
                    End If
                End If
                If cmbTemplate.SelectedIndex <> -1 Then
                    If DirectCast(cmbTemplate.DataSource, DataTable).Rows.Count > 1 Then
                        cmbTemplate.SelectedIndex = 1
                    Else
                        cmbTemplate.SelectedIndex = 0
                    End If
                End If
                _IsChkSheet = True
                If cmbTemplate.SelectedIndex > 0 Then
                    btnRun.Enabled = True
                End If
                lblInProgress.Text = ""
            Else
                btnRun.Enabled = False
                cmbSourceName.SelectedIndex = 0
                cmbTemplate.SelectedIndex = -1
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in cmbSheets_SelectedIndexChanged of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try
    End Sub

#End Region

#Region "RaiseEvents"

    Public Sub Reload(ByVal formatid As Integer) Handles _FundTemplate.ReloadTemplateId
        LoadTemplate()
        cmbTemplate.SelectedValue = formatid
    End Sub

#End Region

#Region "Validations"
    ''' <summary>
    ''' Checks the file consistancy
    ''' </summary>
    ''' <param name="pdtinvestor"></param>
    ''' <param name="excelClone"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ValidateFile(ByRef pdtinvestor As DataTable, ByVal excelClone As DataTable) As Boolean
        Dim DuplicateRecord As New DataTable
        Dim DuplicateFund As New DataTable
        Dim ExcelDuplicateCopy As New DataTable
        Dim DsGenral As New DataSet
        Dim Excel() As DataRow
        Dim ExcelDB() As DataRow
        Dim FilterQuery As String
        Dim InstitutionName As String = ""
        Dim CImportFund As New cImportFunds
        Dim idr As IDataReader = Nothing
        Dim IsInvestorIDExists As Boolean = False
        Dim strStyle As String = ""
        Dim ldtValidationTypes As DataTable = Nothing
        Dim Logmessage As New System.Text.StringBuilder
        Dim IsFundNameExists As Boolean = False
        Dim IsEntityIDExists As Boolean = False
        Dim InsertInc As Integer = 0
        Dim isDuplicate As Boolean = False
        Dim dSets As New DataSet
        Try
            Dim values As Double = 0
            Dim isDouble As Boolean = False

            ldtValidationTypes = New DataTable
            ldtValidationTypes.Columns.Add("Message", GetType(Integer))
            ldtValidationTypes.Columns.Add("Validation Type", GetType(String))

            InstitutionName = "FundName"
            DuplicateRecord = pdtinvestor.Clone()   ''Duplicate record contain the columns of excel sheet only mapped ones.
            DuplicateRecord.Columns.Add("Message", Type.GetType("System.Int32"))
            DuplicateRecord.Columns.Add("InvestorName", Type.GetType("System.String"))
            DuplicateFund.Columns.Add("FndEntityID", Type.GetType("System.String"))
            DuplicateFund.Columns.Add("FundName", Type.GetType("System.String"))
            DuplicateFund.Merge(pdtinvestor, True, MissingSchemaAction.Ignore)    ''Duplicate Fund contain two columns FndEntityID and FundName with data of Excel sheet
            ExcelDuplicateCopy = pdtinvestor.Copy()   ''ExcelDuplicateCopy contains the data of ExcelSheet
            CImportFund.AddColumnsToDatatable(ExcelDuplicateCopy)    '' Columns Added TurnoverID ,Style ,InsertInc etc.
            'idr = CImportFund.GetFundValidatedData()
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 10)

            dSets = CImportFund.GetFundValidatedData()
            'dSets.Tables(0).Merge(DuplicateRecord, False, MissingSchemaAction.Ignore)
            DuplicateRecord.Merge(dSets.Tables(0), False, MissingSchemaAction.Ignore)
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 10)

            Invoke(New GenricDelegate(AddressOf CompleteProgressBar))
            If CommonCLL.CheckEmptyDataTable(DuplicateRecord) Then
                pdtinvestor = ExcelDuplicateCopy.Copy()
                Return True
            End If

            Dim lintColidex As Integer
            lintColidex = 0
            For Each row As DataRow In DuplicateRecord.Rows
                PrepareValidationTypeList(row.Item("Message").ToString(), ldtValidationTypes)
            Next

            MessageBox.Show("Import process has been aborted due to the following inconsistencies", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            If DuplicateRecord.Columns.Contains("FndEntityID") Then
                DuplicateRecord.Columns("FndEntityID").Caption = "Fund ID"
            End If
            If DuplicateRecord.Columns.Contains("FundTypeID") Then
                DuplicateRecord.Columns.Remove("FundTypeID")
                DuplicateRecord.Columns.Remove("StyleID")
                DuplicateRecord.Columns.Remove("TurnoverID")
                DuplicateRecord.Columns.Remove("CountryID")
                DuplicateRecord.Columns.Remove("InvEntityID")
                DuplicateRecord.Columns.Remove("FundID")
                DuplicateRecord.Columns.Remove("EStatusID")
                DuplicateRecord.Columns.Remove("RowNumber")
                DuplicateRecord.Columns.Remove("InvestorName")
            End If
            Me.Invoke(New dlShowValidation(AddressOf ShowValidations), ldtValidationTypes, DuplicateRecord)
            Me.Invoke(New ControlVisiblity(AddressOf ShowNotImportedGrid), DuplicateRecord.Rows.Count > 0)
            Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), True)
            Me.Invoke(New GenricDelegate(AddressOf ClearAfterImportControls))
            InitProgressBar()

        Catch ex As Exception
            Throw ex
            Logmessage.Append("There is error in validate file of form import fund.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
        Finally
            Excel = Nothing
            ExcelDB = Nothing
            DuplicateRecord = Nothing
            DuplicateFund = Nothing
            ExcelDuplicateCopy = Nothing
            FilterQuery = Nothing
            InstitutionName = Nothing
            Logmessage = Nothing
            idr = Nothing
        End Try
        Return False
    End Function
    Private Sub ShowValidations(ByVal ldtValidationTypes, ByVal DuplicateRecord)
        Dim lfrmFundUploadValidations As New frmFundUploadValidations(ldtValidationTypes, DuplicateRecord)
        lfrmFundUploadValidations.WindowState = FormWindowState.Maximized
        AddHandler lfrmFundUploadValidations.ReRunEvent, AddressOf btnReRunClickEvent
        lfrmFundUploadValidations.Show()
    End Sub
    Private Sub ShowNotImportedGrid(ByVal isShow As Boolean)
        If isShow = True Then
            btnReRun.Enabled = True
            btnClose.Enabled = True
        Else

        End If
    End Sub
    Public Sub btnReRunClickEvent()
        btnReRun.PerformClick()
    End Sub
    Private Sub PrepareValidationTypeList(ByVal strMessage As String, ByRef ldtValidationTypes As DataTable)
        Dim FilterQuery As String
        Dim Excel() As DataRow
        Dim ldrValidationType As DataRow
        Try
            FilterQuery = "Message='" + Trim(strMessage).Replace("'", "''") + "'"
            Excel = ldtValidationTypes.Select(FilterQuery)
            If Excel.Length = 0 Then
                ldrValidationType = ldtValidationTypes.NewRow
                ldrValidationType.Item("Message") = strMessage
                ldrValidationType.Item("Validation Type") = GridClick(ldrValidationType.Item("Message"))
                ldtValidationTypes.Rows.Add(ldrValidationType)
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        Finally
            Excel = Nothing
            ldrValidationType = Nothing
        End Try
    End Sub
#End Region

#Region "Grid Event"

    ''' <summary>
    ''' assign the text to label control on grid click
    ''' </summary>
    ''' <remarks></remarks>
    Public Function GridClick(ByVal lintMessage As Integer) As String
        If lintMessage = BLLmdlCommon.GridRecord.InvestorEntityIDExistsInFile Then
            Return "Investor ID exists multiple times."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorEntityIDExistsInDB Then
            Return "Investor ID already exists in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.NumericFund Then
            Return "Fund name cannot be numeric. "
        ElseIf lintMessage = BLLmdlCommon.GridRecord.Numericstate Then
            Return "State name cannot be numeric. "
        ElseIf lintMessage = BLLmdlCommon.GridRecord.Countryblank Then
            Return "Blank Country."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.CountryNotExists Then
            Return "Invalid Country - Please either add to master tables or change in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.FundTypeBlank Then
            Return "Blank Fund Type."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StyleNotExists Then
            Return "Invalid Style - Please either add to master tables or change in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.PortfolioValueNotNum Then
            Return "Invalid Portfolio Value, only numeric entried permitted."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.PortfolioValueNotValid Then
            Return "Invalid Portfolio Value. Entry must be less than 999,999,999,999,999"
        ElseIf lintMessage = BLLmdlCommon.GridRecord.PERatioNotNumeric Then
            Return "Invalid PE Ratio, only numeric entried permitted."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeNotExistsInMDB Then
            Return "Invalid Type - Please either add to master tables or change in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.PERatioNotValid Then
            Return "Invalid PE Ratio. Entry must be less than 999,999,999,999,999"
        ElseIf lintMessage = BLLmdlCommon.GridRecord.PBRatioNotNumeric Then
            Return "Invalid PB Ratio, only numeric entried permitted."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.PBRatioNotValid Then
            Return "Invalid PB Ratio. Entry must be less than 999,999,999,999,999"
        ElseIf lintMessage = BLLmdlCommon.GridRecord.DividentYieldNotNum Then
            Return "Invalid Dividend Yield, only numeric entried permitted."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.DividentYieldNotValid Then
            Return "Invalid Dividend Yield. Entry must be less than 999,999,999,999,999"
        ElseIf lintMessage = BLLmdlCommon.GridRecord.SalesGrowthNotNum Then
            Return "Invalid Sales Growth, only numeric entried permitted."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.SalesGrowthNotValid Then
            Return "Invalid Sales Growth. Entry must be less than 999,999,999,999,999"
        ElseIf lintMessage = BLLmdlCommon.GridRecord.PriceMomentumNotNum Then
            Return "Invalid Price Momentum, only numeric entried permitted."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.PriceMomentumNotValid Then
            Return "Invalid Price Momentum. Entry must be less than 999,999,999,999,999"
        ElseIf lintMessage = BLLmdlCommon.GridRecord.RelativeStrengthNotNum Then
            Return "Invalid Relative Strength, only numeric entried permitted."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.RelativeStrengthNotValid Then
            Return "Invalid Relative Strength. Entry must be less than 999,999,999,999,999"
        ElseIf lintMessage = BLLmdlCommon.GridRecord.Betanotnum Then
            Return "Invalid Beta, only numeric entried permitted."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.BetanotNotValid Then
            Return "Invalid Beta. Entry must be less than 999,999,999,999,999"
        ElseIf lintMessage = BLLmdlCommon.GridRecord.DateNotWellFormated Then
            Return "Date not well formated. "
            'ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorNameExistsInFile Then
            '    Return "Investor Name exists multiple times."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorNotExistsinDB Then
            Return "Unrecognised Investor ID. Please provide existing ID from Investor database or delete in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeBlank Then
            Return "Blank Type."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StyleBlank Then
            Return "Blank Style."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TurnoverNotExistsInDB Then
            Return "Invalid Turnover - Please either add to master tables or change in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TurnoverBlank Then
            Return "Blank Turnover."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvalidFundType Then
            Return "Invalid Fund Type - Please either add to master tables or change in source file.."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.FundExistsMultipleTimes Then
            Return "Fund name exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.FundDuplicate Then
            Return "Multiple instances of Fund and Investor linking."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.FundFieldDifferent Then
            Return "Multiple instances of the same fund but with different fund attribute."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.NonMatchingValues Then
            Return "Fund name is different in database against fund id."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.FundNameExistsInDB Then
            Return "Fund name already exists in the DB but with a different fund ID."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.ManagedFundName Then
            Return "Managed Funds not Permitted."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.FundNameBlank Then
            Return "Fund ID And name missing."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.UnrecognisedFund Then
            Return "Unrecognised Fund ID and Fund Name Missing."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvalidNameCharacter Then
            Return "Invalid Fund name - Only alphanumeric characters are allowed."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.DifferentFundName Then
            Return "Different fund name exists against same fund id in the excel file."
        End If
        Return ""
    End Function

#End Region

#Region "BackUp"
    Private Sub BackUpDatabase()
        Dim srv As Server
        'Reference the AdventureWorks database.
        Dim db As Database
        'Store the current recovery model in a variable.
        'Define a Backup object variable. 
        Dim connString As String = CommonCLL.mstrConnectionstring
        Dim connection As SqlConnection = New SqlConnection(connString)
        Dim database As String = connection.Database.ToString()
        Dim bk As New Backup
        Dim name As String = String.Empty
        Dim backupdate As New Date
        Try
            'Specify the type of backup, the description, the name, and the database to be backed up.
            srv = New Server
            db = srv.Databases("AdventureWorks2008R2")
            bk.Action = BackupActionType.Database
            bk.BackupSetDescription = "Full backup of Crystal"
            bk.BackupSetName = "Crystal Backup"
            bk.Database = database
            'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
            name = DateTime.Now.ToString()
            name = name.Replace(":", "_").Replace("/", "_")
            Dim bdi As BackupDeviceItem
            bdi = New BackupDeviceItem(database + name + ".bak", DeviceType.File)
            'Add the device to the Backup object.
            bk.Devices.Add(bdi)
            'Set the Incremental property to False to specify that this is a full database backup.
            bk.Incremental = False
            'Set the expiration date.
            backupdate = New Date(2012, 10, 5)
            bk.ExpirationDate = backupdate
            'Specify that the log must be truncated after the backup is complete.
            bk.LogTruncation = BackupTruncateLogType.Truncate
            'Run SqlBackup to perform the full database backup on the instance of SQL Server.
            bk.SqlBackup(srv)
            ShowMessageBox(DBResult.DATABASE_BACKUP_CREATED)
            'Inform the user that the backup has been completed.
            'Remove the backup files from the hard disk.
            'My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test_Full_Backup1")
            'My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test_Differential_Backup1")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            connection = Nothing
            database = String.Empty
            bk = Nothing
        End Try
    End Sub
    'Private Sub BackUpDatabase()
    '    Dim srv As Server
    '    srv = New Server
    '    'Reference the AdventureWorks database.
    '    Dim db As Database
    '    db = srv.Databases("AdventureWorks2008R2")
    '    'Store the current recovery model in a variable.
    '    'Dim recoverymod As Integer
    '    'recoverymod = db.DatabaseOptions.RecoveryModel
    '    'Define a Backup object variable. 
    '    Dim bk As New Backup
    '    'Specify the type of backup, the description, the name, and the database to be backed up.
    '    bk.Action = BackupActionType.Database
    '    bk.BackupSetDescription = "Full backup of Crystal"
    '    bk.BackupSetName = "Crystal Backup"
    '    bk.Database = MessageManager.GetMessage("DATABASE_NAME")

    '    'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
    '    Dim name As String = String.Empty
    '    name = DateTime.Now.ToShortDateString()
    '    name = name.Replace(":", "_").Replace("/", "_")
    '    Dim bdi As BackupDeviceItem
    '    bdi = New BackupDeviceItem("Crystal_" + name + "BackUp", DeviceType.File)
    '    'Add the device to the Backup object.
    '    bk.Devices.Add(bdi)
    '    'Set the Incremental property to False to specify that this is a full database backup.
    '    bk.Incremental = False
    '    'Set the expiration date.
    '    Dim backupdate As New Date
    '    backupdate = New Date(2012, 10, 5)
    '    bk.ExpirationDate = backupdate
    '    'Specify that the log must be truncated after the backup is complete.
    '    bk.LogTruncation = BackupTruncateLogType.Truncate
    '    'Run SqlBackup to perform the full database backup on the instance of SQL Server.
    '    bk.SqlBackup(srv)
    '    'Inform the user that the backup has been completed.
    '    'Console.WriteLine("Full Backup complete.")
    '    'Remove the backup device from the Backup object.
    '    'bk.Devices.Remove(bdi)
    '    ''Make a change to the database, in this case, add a table called test_table.
    '    'Dim t As Table
    '    't = New Table(db, "test_table")
    '    'Dim c As Column
    '    'c = New Column(t, "col", DataType.Int)
    '    't.Columns.Add(c)
    '    't.Create()
    '    ''Create another file device for the differential backup and add the Backup object.
    '    'Dim bdid As BackupDeviceItem
    '    'bdid = New BackupDeviceItem("Test_Differential_Backup1", DeviceType.File)
    '    ''Add the device to the Backup object.
    '    'bk.Devices.Add(bdid)
    '    ''Set the Incremental property to True for a differential backup.
    '    'bk.Incremental = True
    '    ''Run SqlBackup to perform the incremental database backup on the instance of SQL Server.
    '    'bk.SqlBackup(srv)
    '    ''Inform the user that the differential backup is complete.
    '    'Console.WriteLine("Differential Backup complete.")
    '    ''Remove the device from the Backup object.
    '    'bk.Devices.Remove(bdid)
    '    ''Delete the AdventureWorks database before restoring it.
    '    'srv.Databases("AdventureWorks").Drop()
    '    ''Define a Restore object variable.
    '    'Dim rs As Restore
    '    'rs = New Restore
    '    ''Set the NoRecovery property to true, so the transactions are not recovered.
    '    'rs.NoRecovery = True
    '    ''Add the device that contains the full database backup to the Restore object.
    '    'rs.Devices.Add(bdi)
    '    ''Specify the database name.
    '    'rs.Database = "AdventureWorks"
    '    ''Restore the full database backup with no recovery.
    '    'rs.SqlRestore(srv)
    '    ''Inform the user that the Full Database Restore is complete.
    '    'Console.WriteLine("Full Database Restore complete.")
    '    ''Remove the device from the Restore object.
    '    'rs.Devices.Remove(bdi)
    '    ''Set te NoRecovery property to False.
    '    'rs.NoRecovery = False
    '    ''Add the device that contains the differential backup to the Restore object.
    '    'rs.Devices.Add(bdid)
    '    ''Restore the differential database backup with recovery.
    '    'rs.SqlRestore(srv)
    '    ''Inform the user that the differential database restore is complete.
    '    'Console.WriteLine("Differential Database Restore complete.")
    '    ''Remove the device.
    '    'rs.Devices.Remove(bdid)
    '    ''Set the database recovery mode back to its original value.
    '    'srv.Databases("AdventureWorks").DatabaseOptions.RecoveryModel = recoverymod
    '    ''Drop the table that was added.
    '    'srv.Databases("AdventureWorks").Tables("test_table").Drop()
    '    'srv.Databases("AdventureWorks").Alter()
    '    'Remove the backup files from the hard disk.
    '    'My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test_Full_Backup1")
    '    'My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test_Differential_Backup1")
    'End Sub

#End Region
End Class